Imports System.Data.OleDb
Imports System.Xml

Public Class LinkUpdater

    Private _conn As New OleDbConnection(My.Settings.PoserFilesConnectionString)


    Private Const UpdateSql As String = "UPDATE Files SET product_link = '{0}' WHERE code = '{1}' AND provider = '{2}'"
    Private Const UpdateSql2 As String = "UPDATE Files SET product_link = '{0}', Requirements = '{1}' WHERE code = '{2}' AND provider = '{3}'"
    Private Const UpdateSql3 As String = "UPDATE Files SET product_link = '{0}', Requirements = '{1}', product_name = '{2}' WHERE code = '{3}' AND provider = '{4}'"
    Private Const UpdateSql4 As String = "UPDATE Files SET product_link = '{0}', Requirements = '{1}', product_name = '{2}', price = '{3}' WHERE code = '{4}' AND provider = '{5}'"
    Private Const UpdateSql5 As String = "UPDATE Files SET product_name = '{0}', price = '{1}' WHERE code = '{2}' AND provider = '{3}'"
    Private Const UpdateSql6 As String = "UPDATE Files SET product_name = '{0}' WHERE code = '{1}' AND provider = '{2}'"

    Private Const SelectFileIdsForCodeSql As String = "SELECT file_id FROM Files WHERE code = '{0}' AND provider = '{1}'"
    Private Const InsertFileForFigureSql As String = "INSERT INTO FileForFigure (file_id, figure) VALUES ({0},'{1}')"

    'Private Const Daz3DProvider As String = "Daz3D"
    'Private Const RdnaProvider As String = "RuntimeDNA"
    'Private Const PoserProsProvider As String = "Poser Pros"

    Private Const FileNotFoundErrorMessage As String = "The remote server returned an error: (404) Not Found."

    Private Const FileNotPresentMessage As String = "{0} file currently not available."
    Private Const FileDownloadMessage As String = "Downloading {0} link file."
    Private Const UpdateMessage As String = "Updating url for product {0}"

    Private Const PROBLEM_APOSTROPHE As String = "'"
    Private Const SAFE_APOSTROPHE As String = "`"

    Private Const Empty As String = ""

    Const PadOne As String = "0"
    Const PadTwo As String = "00"
    Const PadThree As String = "000"

    Private Const FakeRdnaPrice As String = "9999.99"

    Public Event UpdaterMessage(ByVal message As String)

    Private Function GetFigureTranslationDictionary() As Dictionary(Of String, String)

        Dim keys() As String = {"'Millennium Woman' Victoria 1", _
                                "`Millennium Woman` Victoria 1", _
                                "Victoria 2.0", _
                                "Victoria 3.0 Base", _
                                "`Millennium Man` Michael 1", _
                                "'Millennium Man' Michael 1", _
                                "Michael 2.0", _
                                "Michael 3.0 Base", _
                                "Millennium Girls", _
                                "Stephanie 3.0 Petite Base", _
                                "The FREAK", _
                                "Millennium Boys", _
                                "Stephanie", _
                                "Victoria 2.0 Reduced Resolution Figure", _
                                "Aiko 3.0 Base", _
                                "Victoria 4.1 Base", _
                                "Victoria 4.1 Morphs++", _
                                "David Base", _
                                "Millennium Baby 3.0", _
                                "Millennium Kids- Preschoolers", _
                                "Aiko 3.0 Morphs and Maps", _
                                "the Girl", _
                                "Millennium Kids - Young Teens", _
                                "Millennium Dog LE", _
                                "Sara Starter Pack", _
                                "House Mouse", _
                                "Hiro 3.0", _
                                "Nybras", _
                                "Millennium Dragon 2.0", _
                                "Millennium Horse", _
                                "Millenium Horse LE", _
                                "Millennium Cat LE", _
                                "Bryce 6.1", _
                                "DAZ|Studio 1.5"}
        Dim values() As String = {"V1", _
                                  "V1", _
                                  "V2", _
                                  "V3", _
                                  "M1", _
                                  "M1", _
                                  "M2", _
                                  "M3", _
                                  "MilGirls", _
                                  "SP3", _
                                  "Freak", _
                                  "MilBoys", _
                                  "Stephanie", _
                                  "V2RR", _
                                  "A3", _
                                  "V4", _
                                  "V4", _
                                  "David", _
                                  "MilBaby", _
                                  "MilKids - Preschool", _
                                  "A3", _
                                  "Girl", _
                                  "MilKids - Young Teens", _
                                  "MilDog", _
                                  "Sara", _
                                  "House Mouse", _
                                  "H3", _
                                  "Nybras", _
                                  "MilDragon 2", _
                                  "MilHorse", _
                                  "MilHorse", _
                                  "MilCat", _
                                  "Bryce", _
                                  "DazStudio"}

        Dim dict As New Dictionary(Of String, String)

        For counter As Integer = 1 To keys.Length - 1
            dict.Add(keys(counter), _
                     values(counter))
        Next

        Return dict

    End Function

    Private Sub AddFigureFor(ByVal code As String, _
                             ByVal provider As String, _
                             ByVal figure As String)

        Const ExpectedMessage As String = "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.  Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

        Try

            Dim fileId As Integer
            'Dim figureTranslation As Dictionary(Of String, String) = GetFigureTranslationDictionary()

            Dim InsertCommand As OleDbCommand
            Dim SelectCommand As New OleDbCommand
            _conn.Open()
            SelectCommand.Connection = _conn
            SelectCommand.CommandType = CommandType.Text
            SelectCommand.CommandText = String.Format(SelectFileIdsForCodeSql, _
                                                      code, _
                                                      provider)

            Dim results As OleDbDataReader = SelectCommand.ExecuteReader

            While results.Read

                fileId = CType(results(0), Integer)

                InsertCommand = New OleDbCommand
                InsertCommand.Connection = _conn
                InsertCommand.CommandType = CommandType.Text
                InsertCommand.CommandText = String.Format(InsertFileForFigureSql, _
                                                          fileId.ToString, _
                                                          figure)

                Try
                    InsertCommand.ExecuteNonQuery()
                Catch ex As OleDb.OleDbException
                    If ex.Message <> ExpectedMessage Then
                        MsgBox(ex.Message)
                    End If
                End Try

            End While

            results.Close()

        Catch ex As OleDb.OleDbException
            If ex.Message <> ExpectedMessage Then
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub


    Private Sub UpdateLink(ByVal code As String, _
                           ByVal url As String, _
                           ByVal provider As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql, _
                                                      url, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateLink(ByVal code As String, _
                           ByVal url As String, _
                           ByVal provider As String, _
                           ByVal requirements As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql2, _
                                                      url, _
                                                      requirements, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateLink(ByVal code As String, _
                           ByVal url As String, _
                           ByVal provider As String, _
                           ByVal requirements As String, _
                           ByVal productName As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql3, _
                                                      url, _
                                                      requirements, _
                                                      productName, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateLink(ByVal code As String, _
                           ByVal url As String, _
                           ByVal provider As String, _
                           ByVal requirements As String, _
                           ByVal productName As String, _
                           ByVal price As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql4, _
                                                      url, _
                                                      requirements, _
                                                      productName, _
                                                      price, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateName(ByVal code As String, _
                           ByVal provider As String, _
                           ByVal productName As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql6, _
                                                      productName, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateNameAndPrice(ByVal code As String, _
                                   ByVal provider As String, _
                                   ByVal productName As String, _
                                   ByVal price As String)

        Try

            RaiseEvent UpdaterMessage(String.Format(UpdateMessage, code))

            Dim UpdateCommand As New OleDbCommand
            _conn.Open()
            UpdateCommand.Connection = _conn
            UpdateCommand.CommandType = CommandType.Text
            UpdateCommand.CommandText = String.Format(UpdateSql5, _
                                                      productName, _
                                                      price, _
                                                      code, _
                                                      provider)
            UpdateCommand.ExecuteNonQuery()

        Catch ex As OleDbException
            MsgBox(ex.Message)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            If _conn.State <> ConnectionState.Closed Then
                _conn.Close()
            End If
        End Try

    End Sub

    Private Sub UpdateDazLinks(ByVal updatePrice As Boolean)

        Try

            Dim code, url, requirements, productName, price As String
            Dim linksDOM As New XmlDocument
            Dim linkE As XmlElement

            Dim requirement() As String

            RaiseEvent UpdaterMessage(String.Format(FileDownloadMessage, ProviderNames.Daz3D))

            linksDOM.Load(My.Settings.FileUrl & Daz3DDownloader.LINK_FILENAME)

            Dim figureTranslation As Dictionary(Of String, String) = GetFigureTranslationDictionary()

            For Each link As XmlNode In linksDOM.DocumentElement.ChildNodes

                linkE = CType(link, XmlElement)
                code = linkE.GetAttribute(LinkFile.CodeAttributeName)
                url = linkE.GetAttribute(LinkFile.UrlAttributeName)
                requirements = linkE.GetAttribute(LinkFile.RequiredProductsAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                productName = linkE.GetAttribute(LinkFile.ProductNameAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                price = linkE.GetAttribute(LinkFile.PriceAttributeName)

                If updatePrice Then
                    UpdateLink(code, _
                           url, _
                           ProviderNames.Daz3D, _
                           requirements, _
                           productName, _
                           price)
                Else
                    UpdateLink(code, _
                           url, _
                           ProviderNames.Daz3D, _
                           requirements, _
                           productName)
                End If

                'handle the requirements

                If requirements.Length > 0 Then

                    requirement = requirements.Split(Daz3DDownloader.REQUIRED_PRODUCT_SEPERATOR.Trim.ToCharArray)

                    For Each entry As String In requirement

                        If figureTranslation.ContainsKey(entry.Trim) Then
                            AddFigureFor(code, _
                                         ProviderNames.Daz3D, _
                                         figureTranslation(entry.Trim))
                        End If

                    Next

                End If

            Next


        Catch ex As System.Net.WebException
            If ex.Message = FileNotFoundErrorMessage Then
                'no update file present
                RaiseEvent UpdaterMessage(String.Format(FileNotPresentMessage, ProviderNames.Daz3D))
            Else
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub UpdateRenderosityLinks(ByVal updatePrice As Boolean)

        Try

            Dim code, url, requirements, productName, price As String
            Dim linksDOM As New XmlDocument
            Dim linkE As XmlElement

            'Dim requirement() As String

            RaiseEvent UpdaterMessage(String.Format(FileDownloadMessage, ProviderNames.Renderosity))

            linksDOM.Load(My.Settings.FileUrl & RenderosityDownloader.LINK_FILENAME)

            Dim figureTranslation As Dictionary(Of String, String) = GetFigureTranslationDictionary()

            For Each link As XmlNode In linksDOM.DocumentElement.ChildNodes

                linkE = CType(link, XmlElement)
                code = linkE.GetAttribute(LinkFile.CodeAttributeName)
                url = linkE.GetAttribute(LinkFile.UrlAttributeName)
                requirements = linkE.GetAttribute(LinkFile.RequiredProductsAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                productName = linkE.GetAttribute(LinkFile.ProductNameAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                price = linkE.GetAttribute(LinkFile.PriceAttributeName)

                If updatePrice Then
                    UpdateLink(code, _
                           url, _
                           ProviderNames.Renderosity, _
                           requirements, _
                           productName, _
                           price)
                Else
                    UpdateLink(code, _
                           url, _
                           ProviderNames.Renderosity, _
                           requirements, _
                           productName)
                End If

                'handle the requirements

                'If requirements.Length > 0 Then

                '    requirement = requirements.Split(Daz3DDownloader.REQUIRED_PRODUCT_SEPERATOR.Trim.ToCharArray)

                '    For Each entry As String In requirement

                '        If figureTranslation.ContainsKey(entry.Trim) Then
                '            AddFigureFor(code, _
                '                         ProviderNames.Daz3D, _
                '                         figureTranslation(entry.Trim))
                '        End If

                '    Next

                'End If

            Next


        Catch ex As System.Net.WebException
            If ex.Message = FileNotFoundErrorMessage Then
                'no update file present
                RaiseEvent UpdaterMessage(String.Format(FileNotPresentMessage, ProviderNames.Renderosity))
            Else
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

    Private Sub UpdateRuntimeDnaLinks(ByVal updatePrice As Boolean)

        Try

            Dim code, url, productName, price As String
            Dim linksDOM As New XmlDocument
            Dim linkE As XmlElement

            RaiseEvent UpdaterMessage(String.Format(FileDownloadMessage, ProviderNames.RuntimeDNA))

            linksDOM.Load(My.Settings.FileUrl & RDNADownloader.LINK_FILENAME)

            For Each link As XmlNode In linksDOM.DocumentElement.ChildNodes

                linkE = CType(link, XmlElement)
                code = linkE.GetAttribute(LinkFile.CodeAttributeName)
                url = linkE.GetAttribute(LinkFile.UrlAttributeName)
                productName = linkE.GetAttribute(LinkFile.ProductNameAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                price = linkE.GetAttribute(LinkFile.PriceAttributeName)

                If price = FakeRdnaPrice Then
                    price = PadOne
                End If

                UpdateLink(code, _
                           url, _
                           ProviderNames.RuntimeDNA)

                If updatePrice Then
                    UpdateLink(code, _
                           url, _
                           ProviderNames.RuntimeDNA, _
                           Empty, _
                           productName, _
                           price)
                Else
                    UpdateLink(code, _
                           url, _
                           ProviderNames.RuntimeDNA, _
                           Empty, _
                           productName)
                End If


            Next


        Catch ex As System.Net.WebException
            If ex.Message = FileNotFoundErrorMessage Then
                'no update file present
                RaiseEvent UpdaterMessage(String.Format(FileNotPresentMessage, ProviderNames.RuntimeDNA))
            Else
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub


    Public Sub UpdatePoserProsLinks(ByVal updatePrice As Boolean)

        Try

            Dim code, productName, price, paddedCode As String
            Dim linksDOM As New XmlDocument
            Dim linkE As XmlElement

            RaiseEvent UpdaterMessage(String.Format(FileDownloadMessage, ProviderNames.PoserPros))

            linksDOM.Load(My.Settings.FileUrl & PoserProsDownloader.LINK_FILENAME)

            For Each link As XmlNode In linksDOM.DocumentElement.ChildNodes

                linkE = CType(link, XmlElement)
                code = linkE.GetAttribute(LinkFile.CodeAttributeName)
                productName = linkE.GetAttribute(LinkFile.ProductNameAttributeName).Replace(PROBLEM_APOSTROPHE, SAFE_APOSTROPHE)
                price = linkE.GetAttribute(LinkFile.PriceAttributeName)

                Select Case code.Length
                    Case 1
                        paddedCode = PadThree & code
                    Case 2
                        paddedCode = PadTwo & code
                    Case 3
                        paddedCode = PadOne & code
                    Case Else
                        paddedCode = code
                End Select

                If updatePrice Then
                    UpdateNameAndPrice(code, _
                                       ProviderNames.PoserPros, _
                                       productName, _
                                       price)
                    'apply the update a second time to cope with files which have PP infront of the number
                    UpdateNameAndPrice(PoserProsDownloader.CODE_BASE & paddedCode, _
                                       ProviderNames.PoserPros, _
                                       productName, _
                                       price)
                Else
                    UpdateName(code, _
                               ProviderNames.PoserPros, _
                               productName)
                    'apply the update a second time to cope with files which have PP infront of the number
                    UpdateName(PoserProsDownloader.CODE_BASE & paddedCode, _
                               ProviderNames.PoserPros, _
                               productName)
                End If

            Next


        Catch ex As System.Net.WebException
            If ex.Message = FileNotFoundErrorMessage Then
                'no update file present
                RaiseEvent UpdaterMessage(String.Format(FileNotPresentMessage, ProviderNames.PoserPros))
            Else
                MsgBox(ex.Message)
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try


    End Sub

    Public Sub UpdateLinks(ByVal updatePrice As Boolean, _
                           ByVal providerName As String)

        Select Case providerName
            Case ProviderNames.PoserPros
                UpdatePoserProsLinks(updatePrice)
            Case ProviderNames.Daz3D
                UpdateDazLinks(updatePrice)
            Case ProviderNames.Renderosity
                UpdateRenderosityLinks(updatePrice)
            Case ProviderNames.RuntimeDNA
                UpdateRuntimeDnaLinks(updatePrice)
        End Select




    End Sub

End Class
